package com.softer.chengxin.score.dao;

import com.softer.chengxin.common.domin.Page;
import com.softer.chengxin.score.dao.provider.ManagerDaoProvider;
import com.softer.chengxin.score.entity.*;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @author wangixnrui
 * @date 2018/10/12
 */
@Mapper
@Repository
public interface SDManagerDao {

    /**
     * 查询企业信用年度评价列表
     *
     * @param scoreComposite 查询实体
     * @return 结果
     */
    @SelectProvider(type = ManagerDaoProvider.class, method = "getYearScoreDetail")
    List<ScoreComposite> getYearScoreDetail(@Param("sc") ScoreComposite scoreComposite);

    /**
     * 主管部门查看自己管辖区域内企业的 优良/负面信息列表
     *
     * @param infoType 信息种类
     * @param area     区域
     * @param page     页码
     * @param qyName   企业名称
     * @param proName  项目名称
     * @param state    状态
     * @return 结果
     */
    @SelectProvider(type = ManagerDaoProvider.class, method = "getScoreDetailList")
    List<ScoreDetailVO> getScoreDetailList(@Param("infoType") Integer infoType,
                                           @Param("area") String area,
                                           @Param("page") Page page,
                                           @Param("qyName") String qyName,
                                           @Param("proName") String proName,
                                           @Param("state") String state);

    /**
     * 主管部门查看自己管辖区域内企业的 优良/负面信息列表 条数
     *
     * @param infoType 信息种类
     * @param area     区域
     * @param qyName   企业名称
     * @param proName  项目名称
     * @param state    状态
     * @return 结果
     */
    @SelectProvider(type = ManagerDaoProvider.class, method = "getScoreDetailListCount")
    int getScoreDetailListCount(@Param("infoType") Integer infoType,
                                @Param("area") String area,
                                @Param("qyName") String qyName,
                                @Param("proName") String proName,
                                @Param("state") String state);

    /**
     * 查询账号解绑
     *
     * @param name 用户名
     * @return 结果
     */
    @Select("select role_id from manager_user where login_name=#{name}")
    int getUserRoleId(@Param("name") String name);

    /**
     * 删除优良/负面信息
     *
     * @param id id
     * @return 结果
     */
    @Update("DELETE FROM score_detail WHERE id=#{id}")
    int deleteScore(@Param("id") Integer id);

    /**
     * 根据信息ID查询企业ID
     *
     * @param id 信息ID
     * @return 企业ID
     */
    @Select("select corp_id from score_detail where id=#{id}")
    Integer getCorpId(@Param("id") Integer id);

    /**
     * 获取负面/优良信息录入界面的信息列表
     *
     * @param adder   添加人（录入中显示该账号添加的信息）
     * @param qyName  企业名
     * @param proName 项目名
     * @param page    页码
     * @return 结果
     */
    @SelectProvider(type = ManagerDaoProvider.class, method = "getScoreAddList")
    List<ScoreDetailVO> getScoreAddList(@Param("adder") String adder,
                                        @Param("qyName") String qyName,
                                        @Param("proName") String proName,
                                        @Param("infoType") Integer infoType,
                                        @Param("page") Page page);

    @Select("select project_name from company_project where id=#{id}")
    String findProNameById(@Param("id") String projectId);

    /**
     * 获取负面/优良信息录入界面的信息列表 条数
     *
     * @param adder    添加人（录入中显示该账号添加的信息）
     * @param qyName   企业名
     * @param proName  项目名
     * @param infoType 信息种类
     * @return 结果
     */
    @SelectProvider(type = ManagerDaoProvider.class, method = "getScoreAddListCount")
    int getScoreAddListCount(@Param("adder") String adder,
                             @Param("qyName") String qyName,
                             @Param("proName") String proName,
                             @Param("infoType") Integer infoType);

    /**
     * 新增  保存  负面/优良信息
     *
     * @param scoreDetailVO 信息实体
     * @return 结果
     */
    @Insert("insert into score_detail(score_type_id,score,corp_id,project_id,adder,remark,reason,state,approver,approver_time,effective_begin_time," +
            "effective_end_time,info_type,bjdw,bjsj,hjmc,file_path_id,shyj,ssnr,ssfj_id,sssj,hjxm,gmt_create,gmt_modified,publicity_begin_time," +
            "publicity_end_time,score_type_detail_id) values(#{s.scoreTypeId},#{s.score},#{s.corpId},#{s.projectId},#{s.adder},#{s.remark},#{s.reason},#{s.state},#{s.adder}," +
            "#{s.approverTime},#{s.effectiveBeginTime},#{s.effectiveEndTime},#{s.infoType},#{s.bjdw},#{s.bjsj},#{s.hjmc},#{s.filePathId},#{s.shyj}," +
            "#{s.ssnr},#{s.ssfjId},#{s.sssj},#{s.hjxm},now(),#{s.gmtModified},#{s.publicityBeginTime},#{s.publicityEndTime},#{s.scoreTypeDetailId})")
    @Options(useGeneratedKeys = true, keyProperty = "s.id", keyColumn = "id")
    void addScore(@Param("s") ScoreDetailVO scoreDetailVO);

    /**
     * 公示
     *
     * @param adder 登录名
     * @param id    id
     * @return 结果
     */
    @Update("update score_detail set state=2 , gmt_modified=now(),approver_time=now(),publicity_begin_time=now(),publicity_end_time=#{publicEnd},effective_begin_time=#{publicEnd} where id=#{id}")
    int publicity(@Param("adder") String adder,
                  @Param("id") Integer id,
                  @Param("publicEnd") Date publicEnd);

    /**
     * 修改信息
     *
     * @param scoreDetailVO 参数
     * @return 结果
     */
    @Update("update score_detail set score_type_id=#{s.scoreTypeId},score=#{s.score},corp_id=#{s.corpId},project_id=#{s.projectId},adder=#{s.adder},remark=#{s.remark},reason=#{s.reason},state=0," +
            "info_type=#{s.infoType},bjdw=#{s.bjdw},bjsj=#{s.bjsj},file_path_id=#{s.filePathId},effective_end_time=#{s.effectiveEndDate},gmt_modified=now() where id=#{s.id}")
    int update(@Param("s") ScoreDetailVO scoreDetailVO);

    /**
     * 删除保存的信息
     *
     * @param id 信息id
     * @return 结果
     */
    @Delete("delete from score_detail where id=#{id}")
    int delete(@Param("id") Integer id);

    /**
     * 通过id查询信息
     *
     * @param id id
     * @return 结果
     */
    @Select("select d.id as id,d.score_type_id AS scoreTypeId,(SELECT score_name FROM score_type WHERE id = d.score_type_id) AS scoreTypeName,d.score as score," +
            "d.corp_id as corpId,d.project_id as projectId,d.adder as adder,d.remark as remark,d.reason as reason,d.state as state,d.approver as approver," +
            "DATE_FORMAT(d.approver_time, '%Y-%m-%d') as approverTime,d.effective_begin_time as effectiveBeginTime,DATE_FORMAT(d.effective_end_time, '%Y-%m-%d') as effectiveEndTime," +
            "d.info_type as infoType,d.bjdw as bjdw,DATE_FORMAT(d.bjsj, '%Y-%m-%d') as bjsj,d.hjmc as hjmc,d.file_path_id as filePathId," +
            "d.shyj as shyj,d.ssnr as ssnr,d.ssfj_id as ssfjId,d.sssj as sssj,d.hjxm as hjxm,DATE_FORMAT(d.gmt_create, '%Y-%m-%d') as gmtCreate," +
            "DATE_FORMAT(d.gmt_modified, '%Y-%m-%d') as gmtModified,DATE_FORMAT(d.publicity_begin_time, '%Y-%m-%d') as publicityBeginTime," +
            "DATE_FORMAT(d.publicity_end_time, '%Y-%m-%d') as publicityEndTime  from score_detail d where d.id=#{id}")
    ScoreDetailVO findScoreDetailById(@Param("id") Integer id);

    /**
     * 审批优良/负面信息
     *
     * @param id                 信息id
     * @param score              得分
     * @param scoreTypeId        得分类型
     * @param effectiveBeginTime 生效开始时间
     * @param effectiveEndTime   生效结束时间
     * @param remark             备注
     * @param state              状态
     * @return 结果
     */
    @Update("update score_detail set score=#{score},state=#{state},gmt_modified=now(),effective_begin_time=#{effectiveBeginTime},score_type_id=#{scoreTypeId},effective_end_time=#{effectiveEndTime}," +
            "shyj=#{remark},approver=#{loginName},approver_time=now(),state=#{state},publicity_begin_time=now(),publicity_end_time=#{effectiveBeginTime},score_type_detail_id=#{scoreTypeDetailId}  where id=#{id}")
    int sp(@Param("id") Integer id,
           @Param("score") String score,
           @Param("scoreTypeId") Integer scoreTypeId,
           @Param("effectiveBeginTime") String effectiveBeginTime,
           @Param("effectiveEndTime") String effectiveEndTime,
           @Param("remark") String remark,
           @Param("loginName") String loginName,
           @Param("state") Integer state, @Param("scoreTypeDetailId") Integer scoreTypeDetailId);


    /**
     * 审批不通过时调用此sql
     *
     * @param state 状态
     * @return 结果
     */

    @UpdateProvider(type = ManagerDaoProvider.class, method = "spNo")
    int spNo(@Param("id") Integer id,
             @Param("state") Integer state,
             @Param("shyj") String shyj,
             @Param("approver") String approver, @Param("ssnr") String ssnr,
             @Param("score") String score, @Param("scoreTypeId") Integer scoreTypeId);

    /**
     * 变更
     *
     * @param scoreDetailVO 实体
     * @return 结果
     */
    @UpdateProvider(type = ManagerDaoProvider.class, method = "biangeng")
    int biangeng(ScoreDetailVO scoreDetailVO);

    /**
     * 获取该企业 年度 的 得分详细（优良信息，负面信息，基本信息）
     *
     * @param qyName             企业名
     * @param infoType           信息种类
     * @param effectiveBeginTime 公示开始时间
     * @param effectiveEndTime   公示结束时间
     * @param corpId             企业id
     * @param year               年度
     * @param area               管理区域
     * @param page               页码
     * @return 结果集
     */
    @SelectProvider(type = ManagerDaoProvider.class, method = "getScoreDetailByYear")
    List<ScoreDetailVO> getScoreDetailByYear(@Param("area") String area,
                                             @Param("qyName") String qyName,
                                             @Param("infoType") Integer infoType,
                                             @Param("effectiveBeginTime") String effectiveBeginTime,
                                             @Param("effectiveEndTime") String effectiveEndTime,
                                             @Param("corpId") Integer corpId,
                                             @Param("year") String year,
                                             @Param("page") Page page);

    /**
     * 获取该企业 年度 的 得分详细（优良信息，负面信息，基本信息）条数
     *
     * @param qyName             企业名
     * @param infoType           信息种类
     * @param effectiveBeginTime 公示开始时间
     * @param effectiveEndTime   公示结束时间
     * @param corpId             企业id
     * @param year               年度
     * @param area               管理区域
     * @return 结果集
     */
    @SelectProvider(type = ManagerDaoProvider.class, method = "getScoreDetailByYearCount")
    int getScoreDetailByYearCount(@Param("area") String area,
                                  @Param("qyName") String qyName,
                                  @Param("infoType") Integer infoType,
                                  @Param("effectiveBeginTime") String effectiveBeginTime,
                                  @Param("effectiveEndTime") String effectiveEndTime,
                                  @Param("corpId") Integer corpId,
                                  @Param("year") String year);

    /**
     * 得到企业该年度的得分信息
     *
     * @param id   企业id
     * @param year 年度
     * @return 结果
     */
    @Select("SELECT c.id, c.basic_score AS basicScore, c.excellent_score AS excellentScore, c.negative_score AS negativeScore, c.all_score AS allScore,c.company_id AS companyId,c.`level`,c.`year`,c.gmt_create AS gmtCreate,c.gmt_modified AS gmtModified, b.`name` as qyName FROM score_composite c LEFT JOIN company_base_info b ON c.company_id=b.id WHERE c.company_id=#{id} AND c.`year`=#{year}")
    ScoreComposite getScoreComposite(@Param("id") Integer id, @Param("year") String year);

    /**
     * 获取该企业该年度得分详细
     *
     * @param corpId 企业id
     * @return 结果
     */
    @Select("select id as id,score_type_id as scoreTypeId,(SELECT score_name FROM score_type WHERE id = score_type_id) AS scoreTypeName,score as score,info_type as infoType from score_detail " +
            "where corp_id = #{corpId} AND publicity_begin_time <= NOW() AND effective_end_time >= NOW() and state in (2,4,5,6,7)")
    List<ScoreDetail> getScoreDetails(@Param("corpId") Integer corpId);

    /**
     * 修改企业综合评级
     *
     * @param scoreComposite 综合评级实体
     * @return 修改结果
     */
    @Update("UPDATE score_composite SET basic_score =#{sc.basicScore}, excellent_score =#{sc.excellentScore},negative_score= #{sc.negativeScore},all_score=#{sc.allScore},company_id=#{sc.companyId},`level`=#{sc.level},`year`=#{sc.year},gmt_modified=NOW() WHERE id=#{sc.id}")
    int updateScoreComposite(@Param("sc") ScoreComposite scoreComposite);

    /**
     * 新增企业综合评级
     *
     * @param scoreComposite 企业综合评级
     * @return 新增结果
     */
    @Insert("INSERT INTO score_composite(basic_score,excellent_score,negative_score,all_score,company_id,level,year,gmt_create) VALUES(#{sc.basicScore},#{sc.excellentScore},#{sc.negativeScore},#{sc.allScore},#{sc.companyId},#{sc.level},#{sc.year},NOW())")
    int insertScoreComposite(@Param("sc") ScoreComposite scoreComposite);

    /**
     * 查询得分信息详情
     *
     * @param id 得分信息id
     * @return 结果
     */
    @Select("select zg_bgyj AS zgBgyj,c.`name` as qyName,p.project_name as proName,d.id as id,d.score_type_id as scoreTypeId,(SELECT score_name FROM score_type WHERE id = d.score_type_id) AS scoreTypeName,d.score as score,d.corp_id as corpId,d.project_id as projectId,d.adder as adder,d.remark as remark,d.reason as reason,d.state as state,d.approver as approver,DATE_FORMAT(d.approver_time, '%Y-%m-%d') as approverTime,DATE_FORMAT(d.effective_begin_time, '%Y-%m-%d') as effectiveBeginTime,DATE_FORMAT(d.effective_end_time, '%Y-%m-%d') as effectiveEndTime,d.info_type as infoType,d.bjdw as bjdw,DATE_FORMAT(d.bjsj, '%Y-%m-%d') as bjsj,d.hjmc as hjmc,d.file_path_id as filePathId,d.shyj as shyj,d.ssnr as ssnr,d.ssfj_id as ssfjId,d.sssj as sssj,d.hjxm as hjxm,DATE_FORMAT(d.gmt_create, '%Y-%m-%d') as gmtCreate,DATE_FORMAT(d.gmt_modified, '%Y-%m-%d') as gmtModified," +
            "DATE_FORMAT(d.publicity_begin_time, '%Y-%m-%d') as publicityBeginTime,DATE_FORMAT(d.publicity_end_time, '%Y-%m-%d') as publicityEndTime, d.score_type_detail_id as scoreTypeDetailId from score_detail d LEFT JOIN company_base_info c on c.id=d.corp_id LEFT JOIN company_project p ON p.id=d.project_id where d.id=#{id}")
    ScoreDetailVO getDetailScore(@Param("id") Integer id);

    /**
     * 查询企业是否存在有效期范围内的负面信息
     *
     * @param id 企业ID
     * @return 负面信息数量
     */
    @Select("select count(1) from score_detail where corp_id = #{id} and info_type=2 AND publicity_begin_time <= NOW() AND effective_end_time >= NOW() and state in (2,4,5,6,7,9)")
    int getBadInfoNum(@Param("id") Integer id);

    /**
     * 获取企业所有人员数量
     *
     * @param id 企业ID
     * @return 人员数量
     */
    @Select("SELECT COUNT(1) FROM company_person WHERE company_id= #{id}")
    int getAllPersonNum(@Param("id") Integer id);

    /**
     * 获取企业持相关职业资格证书所有人员数量
     *
     * @param id 企业ID
     * @return 人员数量
     */
    @Select("SELECT COUNT(1) FROM company_person WHERE company_id= #{id} and grade_certificate=1")
    int getCertificatePersonNum(@Param("id") Integer id);


    /**
     * 获取企业持相关职业资格证书所有人员数量
     *
     * @param corpId 企业ID
     * @return 持相关职业资格证书所有人员数量
     */
    @Select("SELECT COUNT(1) FROM company_person WHERE company_id=#{id} AND (intermediate_title=2 or intermediate_title=3)")
    int getArtisanScore(@Param("id") Integer corpId);

    /**
     * 获取企业缴纳五险一金的人员数量
     *
     * @param id 企业ID
     * @return 人员数量
     */
    @Select("SELECT COUNT(1) FROM company_person WHERE company_id= #{id} AND insurance=1")
    int getCorpPersonNum(@Param("id") Integer id);

    /**
     * 获取变更列表
     *
     * @param infoType  信息种类
     * @param page      页码
     * @param qyName    企业名
     * @param proName   项目名
     * @param loginName 登录名
     * @param state     状态
     * @return 结果
     */
    @SelectProvider(type = ManagerDaoProvider.class, method = "getBianGengList")
    List<ScoreDetailVO> getBianGengList(@Param("infoType") Integer infoType,
                                        @Param("page") Page page,
                                        @Param("qyName") String qyName,
                                        @Param("proName") String proName,
                                        @Param("loginName") String loginName,
                                        @Param("state") String state, @Param("area") String area);

    /**
     * 获取变更列表 条数
     *
     * @param infoType  信息种类
     * @param qyName    企业名
     * @param proName   项目名
     * @param loginName 登录名
     * @param state     状态
     * @return 结果
     */
    @SelectProvider(type = ManagerDaoProvider.class, method = "getBianGengListCount")
    int getBianGengListCount(@Param("infoType") Integer infoType,
                             @Param("qyName") String qyName,
                             @Param("proName") String proName,
                             @Param("loginName") String loginName,
                             @Param("state") String state, @Param("area") String area);

    /**
     * 通过用户名查找dic_code
     *
     * @param loginName 用户名
     * @return 结果集
     */
    @Select("select dic_code from roules " +
            "where id=(select role_id from manager_user where login_name=#{loginName})")
    String getDicCodeByLoginName(@Param("loginName") String loginName);

    /**
     * 通过id查找dic_code
     *
     * @param id 用户名
     * @return 结果集
     */
    @Select("select dic_code from roules where id=(select role_id from manager_user where id=#{id})")
    String getDicCodeById(@Param("id") Integer id);

    /**
     * 修改企业等级
     *
     * @param scoreComposite 修改实体
     * @return 修改结果
     */
    @Update("UPDATE score_composite SET `level` =#{sc.level} WHERE company_id=#{sc.companyId} AND `year`=#{sc.year}")
    int updateLevel(@Param("sc") ScoreComposite scoreComposite);

    /**
     * 获取省平台推送的
     * 企业信用等级，总得分，优良信息得分，负面信息得分，基础信息得分
     *
     * @param id   企业ID
     * @param year 年度
     * @return 企业信用得分
     */
    @Select("SELECT p.id, p.basic_score AS basicScore, p.excellent_score AS excellentScore, p.negative_score AS negativeScore, p.all_score AS allScore, p.company_id AS companyId,b.`name` as qyName, p.`level`, p.`year`, p.gmt_create AS gmtCreate, p.gmt_modified AS gmtModified FROM score_composite_province p LEFT JOIN company_base_info b ON b.guid=p.company_id  WHERE b.id=#{id} AND p.`year`=#{year}")
    ScoreCompositeProvince getScoreCompositeFromProvince(@Param("id") Integer id, @Param("year") String year);

    /**
     * 查询省平台推送的
     * 企业信用年度评价列表
     *
     * @param scoreComposite 查询实体
     * @return 结果
     */
    @SelectProvider(type = ManagerDaoProvider.class, method = "getYearScoreDetailFromProvince")
    List<ScoreCompositeProvince> getYearScoreDetailFromProvince(@Param("sc") ScoreComposite scoreComposite);


    /**
     * 查询企业信用分数明细
     *
     * @param id   企业Id
     * @param year 年份
     * @return 信用分数明细
     */
    @Select("SELECT id,corp_id as corpId,`year`,companyBasicScore,partyScore,personScore,artisanScore,staffScore,corpManageScore,serviceScore,oldScore,excellentScore,negativeScore FROM `credit` WHERE corp_id=#{id} AND `year`=#{year}")
    Credit getCredit(@Param("id") Integer id, @Param("year") String year);

    /**
     * 新增或修改企业信用分数
     *
     * @param credit 信用分数实体
     * @return 修改数量
     */
    @Insert("INSERT INTO credit (corp_id,year,companyBasicScore,partyScore,personScore,artisanScore,staffScore,corpManageScore,serviceScore,oldScore,excellentScore,negativeScore,gmt_create,gmt_modify) VALUES(#{c.corpId},#{c.year},#{c.companyBasicScore},#{c.partyScore},#{c.personScore},#{c.artisanScore},#{c.staffScore},#{c.corpManageScore},#{c.serviceScore},#{c.oldScore},#{c.excellentScore},#{c.negativeScore},now(),now())" +
            " ON DUPLICATE KEY UPDATE companyBasicScore=#{c.companyBasicScore},partyScore=#{c.partyScore},personScore=#{c.personScore},artisanScore=#{c.artisanScore},staffScore=#{c.staffScore},corpManageScore=#{c.corpManageScore},serviceScore=#{c.serviceScore},oldScore=#{c.oldScore},excellentScore=#{c.excellentScore},negativeScore=#{c.negativeScore},gmt_modify=now()")
    int addOrUpdateCredit(@Param("c") Credit credit);

    /**
     * 查询企业有效优良或者不良信息集合
     *
     * @param id   企业ID
     * @param type 类型（1优良，2不良）
     * @return 信息集合
     */
    @Select("SELECT CONCAT('评分依据:',t.score_name)  as scoreName, CONCAT((CASE d.info_type WHEN 1 THEN '优良信用信息名称:' WHEN 2 THEN '不良信用信息名称:' ELSE '' end),d.reason,(CASE d.info_type WHEN 2 THEN '(-' ELSE '(' end),d.score,'分)') as reason" +
            " FROM score_detail d LEFT JOIN score_type t ON d.score_type_id = t.id" +
            " WHERE d.publicity_begin_time <= DATE_FORMAT(NOW(), '%Y-%m-%d') AND d.effective_end_time >= DATE_FORMAT(NOW(),'%Y-%m-%d') AND d.info_type=#{type} and d.corp_id=#{id}")
    List<Map> getEffectiveList(@Param("id") Integer id, @Param("type") Integer type);

    /**
     * 查询企业下审核通过的项目的数量
     *
     * @param corpId 企业ID
     * @return 审核通过的项目的数量
     */
    @Select("SELECT COUNT(1) FROM company_project WHERE company_id=#{corpId} AND state=2")
    int getPassProjectNum(@Param("corpId") Integer corpId);

    /**
     * 查询企业审核通过的项目下有审核通过的培训的数量
     *
     * @param corpId 企业ID
     * @return 审核通过的培训的数量
     */
    @Select("SELECT COUNT(1) FROM(SELECT i.project_id,COUNT(1) as num FROM traning_detail d LEFT JOIN traning_info i ON i.id =d.traning_id WHERE i.company_id=#{corpId} AND d.state=2 AND FIND_IN_SET(i.project_id,(SELECT GROUP_CONCAT(id) FROM company_project WHERE company_id=#{corpId} AND state=2)) GROUP BY i.project_id)a WHERE a.num>0")
    int getTrainNum(Integer corpId);
}
